{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 开源词向量应用\n",
    "GIT https://github.com/Embedding/Chinese-Word-Vectors  \n",
    "写入SQLite中方便调用   \n",
    "  \n",
    "\"SELECT avg(E001),max(E001),min(E001),avg(E002),max(E002),min(E002) ,avg(E003),max(E003),min(E003)  FROM GitEmbedding\": [  \n",
    "\t{  \n",
    "\t\t\"avg(E001)\" : 0.09579434427446958,  \n",
    "\t\t\"max(E001)\" : 1.521077,  \n",
    "\t\t\"min(E001)\" : -1.492001,  \n",
    "\t\t\"avg(E002)\" : -0.02855578061327285,  \n",
    "\t\t\"max(E002)\" : 1.527217,  \n",
    "\t\t\"min(E002)\" : -1.410738,  \n",
    "\t\t\"avg(E003)\" : 0.06853371376262496,  \n",
    "\t\t\"max(E003)\" : 2.03719,  \n",
    "\t\t\"min(E003)\" : -1.430153  \n",
    "\t}  \n",
    "]}    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sqlite3 Version：2.6.0  \n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import re\n",
    "import sqlite3\n",
    "\n",
    "\n",
    "print('sqlite3 Version：{}  '.format(sqlite3.version))\n",
    "\n",
    "\n",
    "# SQLite 数据库操作\n",
    "class SQLite:\n",
    "    # 初始化 连接数据库\n",
    "    def __init__(self, database, embedding_table, debug=False):\n",
    "        \"\"\"\n",
    "        :param database:         数据库名称\n",
    "        :param embedding_table:  词向量存储表名\n",
    "        \"\"\"\n",
    "\n",
    "        self.debug = debug\n",
    "\n",
    "        # 数据库名称\n",
    "        self.database = database\n",
    "\n",
    "        # 连接数据库\n",
    "        self.db = sqlite3.connect(database=database)\n",
    "        # 使用 cursor() 方法创建一个游标对象 cursor\n",
    "        self.cursor = self.db.cursor()\n",
    "\n",
    "        # 词向量表名\n",
    "        self.embedding_table = embedding_table\n",
    "        print('Connection <{}> Successful. DataBase Version: <{}>'.format(\n",
    "            database, self.select(\"select sqlite_version();\")[0][0]))\n",
    "\n",
    "        # 查看系统中的所有表\n",
    "        db_table = [t_i[0] for t_i in self.select(\"select name from sqlite_master where type='table' order by name\")]\n",
    "        print(\"数据库{} 中的所有表格：{}\".format(database, db_table))\n",
    "\n",
    "    # 创建表格\n",
    "    def create_table(self, table_name, table_info):\n",
    "        \"\"\"\n",
    "        :param table_name:\n",
    "        :param table_info:\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        sql_code = \"CREATE TABLE {} ({});\".format(table_name, table_info)\n",
    "\n",
    "        try:\n",
    "            # 执行sql 语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return \"Success:\\n{}\\nCREATE Successfully!\".format(sql_code)\n",
    "\n",
    "        except Exception as error:\n",
    "            # 如果发生错误就回滚\n",
    "            self.db.rollback()\n",
    "            return \"Error:\\n{}\\nCREATE Error:{}\".format(sql_code, repr(error))\n",
    "\n",
    "    # 删除表格\n",
    "    def drop_table(self, table_name):\n",
    "        sql_code = \"DROP TABLE {}; \".format(table_name)\n",
    "\n",
    "        try:\n",
    "            # 执行sql 语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return \"Success:\\n{}\\nDROP TABLE Successfully!\".format(sql_code)\n",
    "\n",
    "        except Exception as error:\n",
    "            # 如果发生错误就回滚\n",
    "            self.db.rollback()\n",
    "            return \"Error:\\n{}\\nDROP TABLE Error:{}\".format(sql_code, repr(error))\n",
    "\n",
    "    # 插入数据\n",
    "    def insert(self, sql_code):\n",
    "        try:\n",
    "            # 执行sql 语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "\n",
    "            if self.debug:\n",
    "                print(\"Success:\\n{}\\nINSERT Successfully!\".format(sql_code))\n",
    "            return True\n",
    "\n",
    "        except Exception as error:\n",
    "            # 如果发生错误就回滚\n",
    "            self.db.rollback()\n",
    "\n",
    "            print(\"Error:\\n{}\\nINSERT Error:{}\".format(sql_code, repr(error)))\n",
    "            return False\n",
    "\n",
    "    # 数据库查询\n",
    "    def select(self, sql_code):\n",
    "        \"\"\"\n",
    "        :param sql_code:   SQL 查询代码\n",
    "        :return:   返回查询结果\n",
    "        \"\"\"\n",
    "        try:\n",
    "            self.cursor.execute(sql_code)\n",
    "            return self.cursor.fetchall()\n",
    "\n",
    "        except Exception as error:\n",
    "            print(\"Error:\\n{}\\nINSERT Error:{}\".format(sql_code, repr(error)))\n",
    "            return False\n",
    "\n",
    "    # 查询词向量\n",
    "    def search_embedding(self, search_word):\n",
    "        \"\"\"\n",
    "        :param search_word:  待查找词列表\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        sql_select = \"SELECT * FROM {} where word in ('{}')\".format(self.embedding_table, \"','\".join(search_word))\n",
    "        result = self.select(sql_select)\n",
    "\n",
    "        # 以字典形式返回查询的词向量\n",
    "        if result:\n",
    "            return {r_i[0]: r_i[1:] for r_i in result}\n",
    "        else:\n",
    "            return False\n",
    "\n",
    "    # 创建词向量表\n",
    "    def create_embedding_table(self, embedding_len=200):\n",
    "        \"\"\"\n",
    "        :param embedding_len:  词向量长度\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        # 查看系统中的所有表\n",
    "        db_table = [t_i[0] for t_i in self.select(\"select name from sqlite_master where type='table' order by name\")]\n",
    "\n",
    "        if self.embedding_table not in db_table:\n",
    "            # 创建表格\n",
    "            table_information = [\"WORD CHAR(20) PRIMARY KEY\"]  # Word 作为主键\n",
    "            table_information.extend([\"E{:03d} REAL\".format(x) for x in range(1, embedding_len + 1)])\n",
    "\n",
    "            print(self.create_table(self.embedding_table, ','.join(table_information)))\n",
    "\n",
    "    # 插入词向量数据\n",
    "    def insert_embedding_table(self, file, batch=500):\n",
    "        \"\"\"\n",
    "        :param file:    Embedding 文件路径\n",
    "        :param batch:   每批次插入数据库数量\n",
    "        :return:\n",
    "        \"\"\"\n",
    "\n",
    "        #  词典 匹配筛选规则， 删除不规则字符\n",
    "        re_format = re.compile('[\\u4e00-\\u9fa5a-zA-Z·]{1,20}')\n",
    "\n",
    "        # 待插入词\n",
    "        value = \"\"\n",
    "\n",
    "        data = open(file, 'r', encoding='utf-8')\n",
    "        print('{}     shape:{}'.format(file, data.readline()))\n",
    "\n",
    "        # 循环插入词向量\n",
    "        for i, line_i in enumerate(data, start=1):\n",
    "            print('\\r{:<10d}'.format(i), end='')\n",
    "\n",
    "            line_i = line_i.split()\n",
    "\n",
    "            re_match = re.findall(re_format, line_i[0])\n",
    "\n",
    "            if len(re_match) > 0 and len(re_match[0]) == len(line_i[0]):\n",
    "                # 当前词向量\n",
    "                value += (\",('{}',{})\".format(line_i[0], ','.join(line_i[1:])))\n",
    "            else:\n",
    "                print(line_i[0])\n",
    "\n",
    "            # 批量插入数据库\n",
    "            if i % batch == 0:\n",
    "                # 插入数据库\n",
    "                self.insert(\"INSERT INTO {} VALUES {}; \".format(self.embedding_table, value[1:]))\n",
    "\n",
    "                # 清空插入值\n",
    "                value = \"\"\n",
    "\n",
    "            # # # TODO Debug 正式使用时请注释\n",
    "            # if i > 100:\n",
    "            #     break\n",
    "\n",
    "        # 剩余数据 插入数据库\n",
    "        if len(value) > 0:\n",
    "            self.insert(\"INSERT INTO {} VALUES {}; \".format(self.embedding_table, value[1:]))\n",
    "\n",
    "        data.close()\n",
    "\n",
    "    # 关闭数据库\n",
    "    def close(self):\n",
    "        # 关闭 cursor\n",
    "        self.cursor.close()\n",
    "        # 关闭 数据库连接\n",
    "        self.db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection <D:\\PreTrainedModel\\WordEmbedding.db> Successful. DataBase Version: <3.21.0>\n",
      "数据库D:\\PreTrainedModel\\WordEmbedding.db 中的所有表格：['GitEmbedding']\n",
      "词典数量：  [(1036699,)]\n",
      "dict_keys(['Word', 'word', '在'])\n"
     ]
    }
   ],
   "source": [
    "sql = SQLite(r'D:\\PreTrainedModel\\WordEmbedding.db', 'GitEmbedding')\n",
    "\n",
    "# 查看数据\n",
    "print('词典数量： ', sql.select(\"SELECT COUNT(*) FROM GitEmbedding\"))\n",
    "# 删除表格\n",
    "# print(sql.drop_table('embedding'))\n",
    "\n",
    "# # 创建词向量表\n",
    "# sql.create_embedding_table(embedding_len=300)\n",
    "# # 插入词向量\n",
    "# sql.insert_embedding_table(r'D:\\下载\\sgns.merge.word\\sgns.merge.word', batch=5)\n",
    "\n",
    "# 查询词向量\n",
    "print(sql.search_embedding(['word', 'WoRd', 'Word', '在']).keys())\n",
    "\n",
    "# 关闭数据库\n",
    "sql.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PY36-tf12",
   "language": "python",
   "name": "py36_b"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
